﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using Dapper.Database;
using Dapper.Database.Extensions;
using ExpressionExtensionSQL;
using ExpressionExtensionSQL.Dapper;
namespace QuickFramework.Orm
{
    public partial class Repository
    {
        public bool Delete<T>(Expression<Func<T, bool>> expression
                                        , IUnitOfWork unitOfWork = null
                                        , int? commandTimeout = null) where T : Shared.Contracts.EntityBase
        {
            var tableName = GetTableName<T>();
            var sql = $"DELETE FROM {tableName}";
            var param = new Dapper.DynamicParameters();
            if (expression != null)
            {
                var whereSql = expression.ToSql();
                sql += $" WHERE {whereSql.Sql} ";

                foreach (var parameter in whereSql.Parameters)
                {
                    param.Add(parameter.Key, parameter.Value);
                }
            }

            if (unitOfWork != null)
            {
                return unitOfWork.Connection.Execute(sql, param, unitOfWork.Transaction, commandTimeout: commandTimeout) > 0;
            }
            else
            {
                using (var uow = this.UnitOfWork)
                {
                    return uow.Connection.Execute(sql, param, unitOfWork.Transaction, commandTimeout: commandTimeout) > 0;
                }
            }
        }
        public async Task<bool> DeleteAsync<T>(Expression<Func<T, bool>> expression
                                                , IUnitOfWork unitOfWork = null
                                                , int? commandTimeout = null) where T : Shared.Contracts.EntityBase
        {
            var tableName = GetTableName<T>();
            var sql = $"DELETE FROM {tableName}";
            var param = new Dapper.DynamicParameters();
            if (expression != null)
            {
                var whereSql = expression.ToSql();
                sql += $" WHERE {whereSql.Sql}";

                foreach (var parameter in whereSql.Parameters)
                {
                    param.Add(parameter.Key, parameter.Value);
                }
            }

            if (unitOfWork != null)
            {
                return await unitOfWork.Connection.ExecuteAsync(sql, param, unitOfWork.Transaction, commandTimeout: commandTimeout) > 0;
            }
            else
            {
                using (var uow = this.UnitOfWork)
                {
                    return await uow.Connection.ExecuteAsync(sql, param, unitOfWork.Transaction, commandTimeout: commandTimeout) > 0;
                }
            }
        }



        public bool Exists<T>(Expression<Func<T, bool>> expression
                                        , IUnitOfWork unitOfWork = null
                                        , int? commandTimeout = null) where T : Shared.Contracts.EntityBase
        {
            var tableName = GetTableName<T>();
            var sql = $"SELECT COUNT(1) FROM {tableName}";
            var param = new Dapper.DynamicParameters();
            if (expression != null)
            {
                var whereSql = expression.ToSql();
                sql += $" WHERE {whereSql.Sql} ";

                foreach (var parameter in whereSql.Parameters)
                {
                    param.Add(parameter.Key, parameter.Value);
                }
            }

            if (unitOfWork != null)
            {
                return unitOfWork.Connection.ExecuteScalar<int>(sql, param, unitOfWork.Transaction, commandTimeout: commandTimeout) > 0;
            }
            else
            {
                using (var uow = this.UnitOfWork)
                {
                    return uow.Connection.ExecuteScalar<int>(sql, param, unitOfWork.Transaction, commandTimeout: commandTimeout) > 0;
                }
            }
        }
        public async Task<bool> ExistsAsync<T>(Expression<Func<T, bool>> expression
                                                , IUnitOfWork unitOfWork = null
                                                , int? commandTimeout = null) where T : Shared.Contracts.EntityBase
        {
            var tableName = GetTableName<T>();
            var sql = $"SELECT * FROM {tableName}";
            var param = new Dapper.DynamicParameters();
            if (expression != null)
            {
                var whereSql = expression.ToSql();
                sql += $" WHERE {whereSql.Sql}";

                foreach (var parameter in whereSql.Parameters)
                {
                    param.Add(parameter.Key, parameter.Value);
                }
            }

            if (unitOfWork != null)
            {
                return await unitOfWork.Connection.ExecuteScalarAsync<int>(sql, param, unitOfWork.Transaction, commandTimeout: commandTimeout) > 0;
            }
            else
            {
                using (var uow = this.UnitOfWork)
                {
                    return await uow.Connection.ExecuteScalarAsync<int>(sql, param, unitOfWork.Transaction, commandTimeout: commandTimeout) > 0;
                }
            }
        }
        public IEnumerable<T> Find<T>(Expression<Func<T, bool>> expression
                                        , IUnitOfWork unitOfWork = null
                                        , int? commandTimeout = null) where T : Shared.Contracts.EntityBase
        {
            return Where(expression, unitOfWork, commandTimeout);
        }
        public async Task<IEnumerable<T>> FindAsync<T>(Expression<Func<T, bool>> expression
                                            , IUnitOfWork unitOfWork = null
                                            , int? commandTimeout = null) where T : Shared.Contracts.EntityBase
        {
            return await WhereAsync(expression, unitOfWork, commandTimeout);
        }

        public IEnumerable<T> Where<T>(Expression<Func<T, bool>> expression
                                , IUnitOfWork unitOfWork = null
                                , int? commandTimeout = null) where T : Shared.Contracts.EntityBase
        {
            var tableName = GetTableName<T>();
            var sql = $"SELECT * FROM {tableName}";
            var param = new Dapper.DynamicParameters();
            if (expression != null)
            {
                var whereSql = expression.ToSql();
                sql += $" WHERE {whereSql.Sql}";

                foreach (var parameter in whereSql.Parameters)
                {
                    param.Add(parameter.Key, parameter.Value);
                }
            }

            if (unitOfWork != null)
            {
                return unitOfWork.Connection.Query<T>(sql, param, unitOfWork.Transaction, commandTimeout: commandTimeout);
            }
            else
            {
                using (var uow = this.UnitOfWork)
                {
                    return uow.Connection.Query<T>(sql, param, unitOfWork.Transaction, commandTimeout: commandTimeout);
                }
            }
        }
        public async Task<IEnumerable<T>> WhereAsync<T>(Expression<Func<T, bool>> expression
                                                , IUnitOfWork unitOfWork = null
                                                , int? commandTimeout = null) where T : Shared.Contracts.EntityBase
        {
            var tableName = GetTableName<T>();
            var sql = $"SELECT * FROM {tableName}";
            var param = new Dapper.DynamicParameters();
            if (expression != null)
            {
                var whereSql = expression.ToSql();
                sql += $" WHERE {whereSql.Sql}";

                foreach (var parameter in whereSql.Parameters)
                {
                    param.Add(parameter.Key, parameter.Value);
                }
            }

            if (unitOfWork != null)
            {
                return await unitOfWork.Connection.QueryAsync<T>(sql, param, unitOfWork.Transaction, commandTimeout: commandTimeout);
            }
            else
            {
                using (var uow = this.UnitOfWork)
                {
                    return await uow.Connection.QueryAsync<T>(sql, param, unitOfWork.Transaction, commandTimeout: commandTimeout);
                }
            }
        }
        public T FirstOrDefault<T>(Expression<Func<T, bool>> expression
                                    , IUnitOfWork unitOfWork = null
                                    , int? commandTimeout = null) where T : Shared.Contracts.EntityBase
        {
            var tableName = GetTableName<T>();
            var sql = $"SELECT * FROM {tableName}";
            var param = new Dapper.DynamicParameters();
            if (expression != null)
            {
                var whereSql = expression.ToSql();
                sql += $"  WHERE {whereSql.Sql}";

                foreach (var parameter in whereSql.Parameters)
                {
                    param.Add(parameter.Key, parameter.Value);
                }
            }

            if (unitOfWork != null)
            {
                return unitOfWork.Connection.QueryFirstOrDefault<T>(  sql
                                                                    , param
                                                                    , unitOfWork.Transaction
                                                                    , commandTimeout: commandTimeout);
            }
            else
            {
                using (var uow = this.UnitOfWork)
                {
                    return uow.Connection.QueryFirstOrDefault<T>( sql
                                                                , param
                                                                , unitOfWork.Transaction
                                                                , commandTimeout: commandTimeout);
                }
            }
        }
        public async Task<T> FirstOrDefaultAsync<T>(Expression<Func<T, bool>> expression
                                                    , IUnitOfWork unitOfWork = null
                                                    , int? commandTimeout = null) where T : Shared.Contracts.EntityBase
        {
            var tableName = GetTableName<T>();
            var sql = $"SELECT * FROM {tableName}";
            var param = new Dapper.DynamicParameters();
            if (expression != null)
            {
                var whereSql = expression.ToSql();
                sql += $" WHERE {whereSql.Sql}";
                foreach (var parameter in whereSql.Parameters)
                {
                    param.Add(parameter.Key, parameter.Value);
                }
            }
            if (unitOfWork != null)
            {
                return await unitOfWork.Connection.QueryFirstOrDefaultAsync<T>(sql
                                                                    , param
                                                                    , unitOfWork.Transaction
                                                                    , commandTimeout: commandTimeout);
            }
            else
            {
                using (var uow = this.UnitOfWork)
                {
                    return await uow.Connection.QueryFirstOrDefaultAsync<T>(sql
                                                                , param
                                                                , unitOfWork.Transaction
                                                                , commandTimeout: commandTimeout);
                }
            }
        }

    }
}
